import { parseExcelFile_SERVER } from '../lib/excel-parser.server';
import { SqliteDatabase } from '../lib/sqlite-database';
import * as fs from 'fs';
import * as path from 'path';

interface FieldMappingResult {
  tableName: string;
  status: 'MATCH' | 'MISSING_FIELDS' | 'EXTRA_FIELDS' | 'CHARACTER_DIFFERENCES' | 'ERROR';
  exactMatches: string[];
  missingFields: string[];
  extraFields: string[];
  characterDifferences: Array<{
    excel: string;
    database: string;
    difference: string;
  }>;
  totalExcelFields: number;
  totalDatabaseFields: number;
  matchPercentage: number;
  errorMessage?: string;
}

interface TableMapping {
  excelSheetName: string;
  databaseTableName: string;
  tableCode: string;
  description: string;
}

interface SpecialCharacterAnalysis {
  field: string;
  hasSlash: boolean;
  hasParentheses: boolean;
  hasSpecialPunctuation: boolean;
  isMultiLine: boolean;
  specialCharacters: string[];
}

class ComprehensiveFieldVerifier {
  private db: SqliteDatabase;
  private results: FieldMappingResult[] = [];
  private specialCharacterAnalysis: SpecialCharacterAnalysis[] = [];
  
  constructor() {
    this.db = new SqliteDatabase();
  }

  /**
   * Main verification method - checks all Excel files against database tables
   */
  async verifyAllFieldMappings(): Promise<{
    results: FieldMappingResult[];
    summary: {
      totalTables: number;
      perfectMatches: number;
      partialMatches: number;
      failures: number;
      overallMatchPercentage: number;
    };
    specialCharacterAnalysis: SpecialCharacterAnalysis[];
  }> {
    console.log('🔍 Starting comprehensive field verification...');
    
    try {
      // 1. Get all table mappings from database
      const tableMappings = await this.getTableMappings();
      console.log(`📊 Found ${tableMappings.length} table mappings`);
      
      // 2. Process each Excel file
      const excelFiles = this.getExcelFiles();
      console.log(`📁 Found ${excelFiles.length} Excel files`);
      
      // 3. Verify each mapping
      for (const excelFile of excelFiles) {
        console.log(`\n📄 Processing Excel file: ${excelFile}`);
        await this.verifyExcelFile(excelFile, tableMappings);
      }
      
      // 4. Generate summary
      const summary = this.generateSummary();
      
      // 5. Generate detailed report
      await this.generateDetailedReport();
      
      return {
        results: this.results,
        summary,
        specialCharacterAnalysis: this.specialCharacterAnalysis
      };
      
    } catch (error) {
      console.error('❌ Error during verification:', error);
      throw error;
    }
  }

  /**
   * Get all table mappings from database
   */
  private async getTableMappings(): Promise<TableMapping[]> {
    const mappings: TableMapping[] = [];
    
    try {
      // Get all table_c tables from database
      const tables = this.db.all(`
        SELECT name FROM sqlite_master 
        WHERE type='table' AND name LIKE 'table_c%'
        ORDER BY name
      `);
      
      for (const table of tables) {
        const tableName = table.name;
        const tableCode = tableName.replace('table_c', 'C');
        
        // Try to get description from metadata
        const metadata = this.db.get(`
          SELECT description FROM parameter_table_metadata 
          WHERE table_name = ? OR table_code = ?
        `, [tableName, tableCode]);
        
        mappings.push({
          excelSheetName: `C${tableName.replace('table_c', '')}`, // Convert table_c123 to C123
          databaseTableName: tableName,
          tableCode,
          description: metadata?.description || `Table ${tableCode}`
        });
      }
      
      return mappings;
    } catch (error) {
      console.error('Error getting table mappings:', error);
      return [];
    }
  }

  /**
   * Get all available Excel files
   */
  private getExcelFiles(): string[] {
    const excelDirs = [
      path.join(process.cwd(), 'excel', '06-06'),
      path.join(process.cwd(), 'excel', '06-18'),
      path.join(process.cwd(), 'excel', '06-21')
    ];
    
    const files: string[] = [];
    
    for (const dir of excelDirs) {
      if (fs.existsSync(dir)) {
        const dirFiles = fs.readdirSync(dir)
          .filter(f => f.endsWith('.xlsx') || f.endsWith('.xls'))
          .map(f => path.join(dir, f));
        files.push(...dirFiles);
      }
    }
    
    return files;
  }

  /**
   * Verify a single Excel file against database mappings
   */
  private async verifyExcelFile(excelFilePath: string, tableMappings: TableMapping[]): Promise<void> {
    try {
      const parsedFile = parseExcelFile_SERVER(excelFilePath);
      console.log(`  📋 Parsed ${parsedFile.sheets.length} sheets`);
      
      for (const sheet of parsedFile.sheets) {
        // Find corresponding database table
        const mapping = tableMappings.find(m => 
          m.excelSheetName === sheet.sheetName || 
          sheet.sheetName.includes(m.excelSheetName) ||
          m.excelSheetName.includes(sheet.sheetName)
        );
        
        if (!mapping) {
          console.log(`    ⚠️  No mapping found for sheet: ${sheet.sheetName}`);
          continue;
        }
        
        console.log(`    🔗 Verifying sheet ${sheet.sheetName} -> ${mapping.databaseTableName}`);
        
        // Extract and verify fields
        const result = await this.verifyFieldMapping(
          sheet.sheetName,
          sheet.headers,
          mapping.databaseTableName,
          mapping.description
        );
        
        this.results.push(result);
        
        // Analyze special characters
        this.analyzeSpecialCharacters(sheet.headers);
      }
      
    } catch (error) {
      console.error(`Error processing Excel file ${excelFilePath}:`, error);
      this.results.push({
        tableName: path.basename(excelFilePath),
        status: 'ERROR',
        exactMatches: [],
        missingFields: [],
        extraFields: [],
        characterDifferences: [],
        totalExcelFields: 0,
        totalDatabaseFields: 0,
        matchPercentage: 0,
        errorMessage: error instanceof Error ? error.message : 'Unknown error'
      });
    }
  }

  /**
   * Verify field mapping between Excel headers and database columns
   */
  private async verifyFieldMapping(
    sheetName: string,
    excelHeaders: string[],
    databaseTableName: string,
    description: string
  ): Promise<FieldMappingResult> {
    try {
      // Get database column names
      const columns = this.db.all(`PRAGMA table_info(${databaseTableName})`);
      const dbColumns = columns
        .filter(col => !['id', 'created_at', 'updated_at'].includes(col.name))
        .map(col => col.name);
      
      // Clean and normalize field names for comparison
      const cleanExcelHeaders = excelHeaders
        .filter(h => h && h.trim() !== '')
        .map(h => h.trim());
      
      const cleanDbColumns = dbColumns.map(col => col.trim());
      
      // Find exact matches
      const exactMatches: string[] = [];
      const missingFields: string[] = [];
      const extraFields: string[] = [];
      const characterDifferences: Array<{
        excel: string;
        database: string;
        difference: string;
      }> = [];
      
      // Check each Excel header against database columns
      for (const excelHeader of cleanExcelHeaders) {
        const exactMatch = cleanDbColumns.find(dbCol => 
          this.isExactMatch(excelHeader, dbCol)
        );
        
        if (exactMatch) {
          exactMatches.push(excelHeader);
        } else {
          // Look for close matches with character differences
          const closeMatch = cleanDbColumns.find(dbCol => 
            this.isCloseMatch(excelHeader, dbCol)
          );
          
          if (closeMatch) {
            const difference = this.findCharacterDifference(excelHeader, closeMatch);
            characterDifferences.push({
              excel: excelHeader,
              database: closeMatch,
              difference
            });
          } else {
            missingFields.push(excelHeader);
          }
        }
      }
      
      // Check for extra fields in database
      for (const dbCol of cleanDbColumns) {
        const foundInExcel = cleanExcelHeaders.find(excelHeader => 
          this.isExactMatch(excelHeader, dbCol) || 
          this.isCloseMatch(excelHeader, dbCol)
        );
        
        if (!foundInExcel) {
          extraFields.push(dbCol);
        }
      }
      
      // Calculate match percentage
      const totalExcelFields = cleanExcelHeaders.length;
      const totalDatabaseFields = cleanDbColumns.length;
      const matchedFields = exactMatches.length;
      const matchPercentage = totalExcelFields > 0 
        ? (matchedFields / totalExcelFields) * 100 
        : 0;
      
      // Determine status
      let status: FieldMappingResult['status'] = 'MATCH';
      if (missingFields.length > 0) status = 'MISSING_FIELDS';
      if (extraFields.length > 0) status = 'EXTRA_FIELDS';
      if (characterDifferences.length > 0) status = 'CHARACTER_DIFFERENCES';
      if (matchPercentage === 100 && missingFields.length === 0 && extraFields.length === 0 && characterDifferences.length === 0) {
        status = 'MATCH';
      }
      
      return {
        tableName: `${sheetName} -> ${databaseTableName}`,
        status,
        exactMatches,
        missingFields,
        extraFields,
        characterDifferences,
        totalExcelFields,
        totalDatabaseFields,
        matchPercentage: Math.round(matchPercentage * 100) / 100
      };
      
    } catch (error) {
      console.error(`Error verifying field mapping for ${databaseTableName}:`, error);
      return {
        tableName: `${sheetName} -> ${databaseTableName}`,
        status: 'ERROR',
        exactMatches: [],
        missingFields: [],
        extraFields: [],
        characterDifferences: [],
        totalExcelFields: 0,
        totalDatabaseFields: 0,
        matchPercentage: 0,
        errorMessage: error instanceof Error ? error.message : 'Unknown error'
      };
    }
  }

  /**
   * Check if two field names are exactly the same
   */
  private isExactMatch(excel: string, database: string): boolean {
    // Character-by-character comparison
    return excel === database;
  }

  /**
   * Check if two field names are close matches (allowing for minor differences)
   */
  private isCloseMatch(excel: string, database: string): boolean {
    // Remove common variations
    const normalizeField = (field: string) => {
      return field
        .replace(/\s+/g, '') // Remove all spaces
        .replace(/[_\-]/g, '') // Remove underscores and dashes
        .toLowerCase();
    };
    
    const normalizedExcel = normalizeField(excel);
    const normalizedDb = normalizeField(database);
    
    // Check if normalized versions match
    if (normalizedExcel === normalizedDb) return true;
    
    // Check for common Chinese punctuation variations
    const chineseVariations: Array<[RegExp, string]> = [
      [/（/g, '('],
      [/）/g, ')'],
      [/／/g, '/'],
      [/。/g, '.'],
      [/，/g, ','],
      [/：/g, ':'],
      [/；/g, ';']
    ];
    
    let excelVariant = excel;
    let dbVariant = database;
    
    for (const [from, to] of chineseVariations) {
      excelVariant = excelVariant.replace(from, to);
      dbVariant = dbVariant.replace(from, to);
    }
    
    return excelVariant === dbVariant;
  }

  /**
   * Find the specific character difference between two field names
   */
  private findCharacterDifference(excel: string, database: string): string {
    const differences: string[] = [];
    
    // Character-by-character comparison
    const maxLength = Math.max(excel.length, database.length);
    
    for (let i = 0; i < maxLength; i++) {
      const excelChar = excel[i] || '';
      const dbChar = database[i] || '';
      
      if (excelChar !== dbChar) {
        differences.push(`Position ${i}: '${excelChar}' vs '${dbChar}'`);
      }
    }
    
    if (excel.length !== database.length) {
      differences.push(`Length difference: ${excel.length} vs ${database.length}`);
    }
    
    return differences.join('; ');
  }

  /**
   * Analyze special characters in field names
   */
  private analyzeSpecialCharacters(headers: string[]): void {
    for (const header of headers) {
      if (!header || header.trim() === '') continue;
      
      const analysis: SpecialCharacterAnalysis = {
        field: header,
        hasSlash: header.includes('/') || header.includes('／'),
        hasParentheses: header.includes('(') || header.includes(')') || header.includes('（') || header.includes('）'),
        hasSpecialPunctuation: /[，。：；！？""'']/g.test(header),
        isMultiLine: header.includes('\n') || header.includes('\r'),
        specialCharacters: []
      };
      
      // Extract all special characters
      const specialChars = header.match(/[^\w\s\u4e00-\u9fff]/g) || [];
      analysis.specialCharacters = [...new Set(specialChars)];
      
      // Add to analysis if it has special characteristics
      if (analysis.hasSlash || analysis.hasParentheses || analysis.hasSpecialPunctuation || analysis.isMultiLine) {
        this.specialCharacterAnalysis.push(analysis);
      }
    }
  }

  /**
   * Generate summary statistics
   */
  private generateSummary() {
    const totalTables = this.results.length;
    const perfectMatches = this.results.filter(r => r.status === 'MATCH').length;
    const partialMatches = this.results.filter(r => 
      r.status !== 'MATCH' && r.status !== 'ERROR' && r.matchPercentage > 0
    ).length;
    const failures = this.results.filter(r => r.status === 'ERROR').length;
    
    const totalMatchPercentage = this.results.length > 0 
      ? this.results.reduce((sum, r) => sum + r.matchPercentage, 0) / this.results.length
      : 0;
    
    return {
      totalTables,
      perfectMatches,
      partialMatches,
      failures,
      overallMatchPercentage: Math.round(totalMatchPercentage * 100) / 100
    };
  }

  /**
   * Generate detailed report
   */
  private async generateDetailedReport(): Promise<void> {
    const timestamp = new Date().toISOString().replace(/[:.]/g, '-');
    const reportPath = path.join(process.cwd(), `field-verification-report-${timestamp}.json`);
    
    const report = {
      timestamp: new Date().toISOString(),
      summary: this.generateSummary(),
      results: this.results,
      specialCharacterAnalysis: this.specialCharacterAnalysis,
      detailedFindings: {
        fieldsWithSlashes: this.specialCharacterAnalysis.filter(a => a.hasSlash),
        fieldsWithParentheses: this.specialCharacterAnalysis.filter(a => a.hasParentheses),
        fieldsWithSpecialPunctuation: this.specialCharacterAnalysis.filter(a => a.hasSpecialPunctuation),
        multiLineFields: this.specialCharacterAnalysis.filter(a => a.isMultiLine)
      }
    };
    
    fs.writeFileSync(reportPath, JSON.stringify(report, null, 2));
    console.log(`\n📊 Detailed report saved to: ${reportPath}`);
  }

  /**
   * Print console report
   */
  printReport(): void {
    console.log('\n' + '='.repeat(80));
    console.log('📋 COMPREHENSIVE FIELD VERIFICATION REPORT');
    console.log('='.repeat(80));
    
    const summary = this.generateSummary();
    
    console.log('\n📊 SUMMARY:');
    console.log(`   Total Tables Verified: ${summary.totalTables}`);
    console.log(`   Perfect Matches: ${summary.perfectMatches} (${((summary.perfectMatches / summary.totalTables) * 100).toFixed(1)}%)`);
    console.log(`   Partial Matches: ${summary.partialMatches} (${((summary.partialMatches / summary.totalTables) * 100).toFixed(1)}%)`);
    console.log(`   Failures: ${summary.failures} (${((summary.failures / summary.totalTables) * 100).toFixed(1)}%)`);
    console.log(`   Overall Match Percentage: ${summary.overallMatchPercentage}%`);
    
    console.log('\n🔍 DETAILED RESULTS:');
    
    // Group results by status
    const byStatus = {
      MATCH: this.results.filter(r => r.status === 'MATCH'),
      MISSING_FIELDS: this.results.filter(r => r.status === 'MISSING_FIELDS'),
      EXTRA_FIELDS: this.results.filter(r => r.status === 'EXTRA_FIELDS'),
      CHARACTER_DIFFERENCES: this.results.filter(r => r.status === 'CHARACTER_DIFFERENCES'),
      ERROR: this.results.filter(r => r.status === 'ERROR')
    };
    
    // Print perfect matches
    if (byStatus.MATCH.length > 0) {
      console.log(`\n✅ PERFECT MATCHES (${byStatus.MATCH.length}):`);
      byStatus.MATCH.forEach(result => {
        console.log(`   ${result.tableName} - ${result.exactMatches.length} fields`);
      });
    }
    
    // Print missing fields
    if (byStatus.MISSING_FIELDS.length > 0) {
      console.log(`\n❌ MISSING FIELDS (${byStatus.MISSING_FIELDS.length}):`);
      byStatus.MISSING_FIELDS.forEach(result => {
        console.log(`   ${result.tableName} - Missing: ${result.missingFields.length} fields`);
        result.missingFields.forEach(field => {
          console.log(`     • "${field}"`);
        });
      });
    }
    
    // Print extra fields
    if (byStatus.EXTRA_FIELDS.length > 0) {
      console.log(`\n⚠️  EXTRA FIELDS (${byStatus.EXTRA_FIELDS.length}):`);
      byStatus.EXTRA_FIELDS.forEach(result => {
        console.log(`   ${result.tableName} - Extra: ${result.extraFields.length} fields`);
        result.extraFields.forEach(field => {
          console.log(`     • "${field}"`);
        });
      });
    }
    
    // Print character differences
    if (byStatus.CHARACTER_DIFFERENCES.length > 0) {
      console.log(`\n🔍 CHARACTER DIFFERENCES (${byStatus.CHARACTER_DIFFERENCES.length}):`);
      byStatus.CHARACTER_DIFFERENCES.forEach(result => {
        console.log(`   ${result.tableName}:`);
        result.characterDifferences.forEach(diff => {
          console.log(`     Excel: "${diff.excel}"`);
          console.log(`     Database: "${diff.database}"`);
          console.log(`     Difference: ${diff.difference}`);
          console.log('');
        });
      });
    }
    
    // Print errors
    if (byStatus.ERROR.length > 0) {
      console.log(`\n💥 ERRORS (${byStatus.ERROR.length}):`);
      byStatus.ERROR.forEach(result => {
        console.log(`   ${result.tableName}: ${result.errorMessage}`);
      });
    }
    
    // Print special character analysis
    if (this.specialCharacterAnalysis.length > 0) {
      console.log('\n🔤 SPECIAL CHARACTER ANALYSIS:');
      
      const withSlashes = this.specialCharacterAnalysis.filter(a => a.hasSlash);
      if (withSlashes.length > 0) {
        console.log(`\n   Fields with slashes (${withSlashes.length}):`);
        withSlashes.forEach(analysis => {
          console.log(`     • "${analysis.field}"`);
        });
      }
      
      const withParentheses = this.specialCharacterAnalysis.filter(a => a.hasParentheses);
      if (withParentheses.length > 0) {
        console.log(`\n   Fields with parentheses (${withParentheses.length}):`);
        withParentheses.forEach(analysis => {
          console.log(`     • "${analysis.field}"`);
        });
      }
      
      const withSpecialPunctuation = this.specialCharacterAnalysis.filter(a => a.hasSpecialPunctuation);
      if (withSpecialPunctuation.length > 0) {
        console.log(`\n   Fields with special punctuation (${withSpecialPunctuation.length}):`);
        withSpecialPunctuation.forEach(analysis => {
          console.log(`     • "${analysis.field}" - Characters: ${analysis.specialCharacters.join(', ')}`);
        });
      }
    }
    
    console.log('\n' + '='.repeat(80));
    console.log('✅ VERIFICATION COMPLETE');
    console.log('='.repeat(80));
  }

  /**
   * Close database connection
   */
  close(): void {
    this.db.close();
  }
}

// Main execution function
async function main() {
  const verifier = new ComprehensiveFieldVerifier();
  
  try {
    console.log('🚀 Starting comprehensive field verification...');
    
    const results = await verifier.verifyAllFieldMappings();
    
    // Print console report
    verifier.printReport();
    
    // Check for critical issues
    const criticalIssues = results.results.filter(r => 
      r.status === 'ERROR' || r.matchPercentage < 50
    );
    
    if (criticalIssues.length > 0) {
      console.log(`\n⚠️  CRITICAL ISSUES FOUND: ${criticalIssues.length} tables have serious problems`);
      process.exit(1);
    } else {
      console.log('\n✅ All field mappings verified successfully!');
      process.exit(0);
    }
    
  } catch (error) {
    console.error('💥 Fatal error during verification:', error);
    process.exit(1);
  } finally {
    verifier.close();
  }
}

// Run if called directly
if (require.main === module) {
  main().catch(console.error);
}

export type { FieldMappingResult, SpecialCharacterAnalysis };
export { ComprehensiveFieldVerifier };